/*== TSL_prc_Interval_TestResults ===============================================================
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE TSL_prc_Interval_TestResults
		@RunId  INT
		,@StartOffset INT = NULL
		,@EndOffset INT = NULL
		,@NameFilter nvarchar(255) = '%'
		,@IncludeFailedTests INT = 0
		,@OrderByAverageTime INT = 1
		,@IncludeWarmupCooldown INT = 0
		,@NumberOfRows INT = 25
	AS

	DECLARE @StartTime DATETIME
	SET @StartTime = (SELECT StartTime FROM LoadTestRun WHERE LoadTestRunId = @RunId)

		DECLARE @Results TABLE ([id] int, [Name] NVARCHAR(255), TestCaseId INT, [Total] INT, [Passed] INT,
			[Minimum] DECIMAL(15,2),[Average] DECIMAL(15,2), [Maximum] DECIMAL(15,2), 
			[Std Dev] DECIMAL(15,2), [90Percent] DECIMAL(15,2),	[95Percent] DECIMAL(15,2))

		INSERT INTO @Results 
		([id],[Name], TestCaseId, [Total], [Passed] ,[Minimum],[Average],[Maximum],[Std Dev])
			SELECT
				a.TestCaseId   -- added to keep the table shape identical to the transactionbyinterval sproc
				,c.TestCaseName
				,a.TestCaseId
				,COUNT(DISTINCT (a.TestDetailId)) AS [Total]
				,COUNT(DISTINCT (a2.TestDetailId)) AS [Passed]
				,CAST(MIN(a2.ElapsedTime) AS Decimal(15,2)) AS [Min]
				,CAST(AVG(a2.ElapsedTime) AS Decimal(15,2)) AS [Avg]
				,CAST(MAX(a2.ElapsedTime) AS Decimal(15,2)) AS [Max]
				,CAST(STDEV(a2.ElapsedTime) AS Decimal(15,2)) AS [Std Dev]
			FROM 
				dbo.LoadTestTestDetail AS a
			LEFT OUTER JOIN LoadTestTestDetail AS a2
				ON a.LoadTestRunId = a2.LoadTestRunId
				AND a.TestDetailId = a2.TestDetailId
				AND a2.Outcome = 10
			INNER JOIN 
				LoadTestRun AS b 
				ON a.LoadTestRunId = b.LoadTestRunId
			INNER JOIN
				dbo.LoadTestCase AS c
				ON a.LoadTestRunId = c.LoadTestRunId
				AND a.TestCaseId = c.TestCaseId		
			WHERE 
				a.LoadTestRunId = @RunId
				AND (@StartOffset IS NULL OR a.EndTime >= DATEADD(second, @StartOffset, b.StartTime))
				AND (@EndOffset IS NULL OR a.EndTime < DATEADD(second, @EndOffset, b.StartTime))
				AND (@IncludeFailedTests = 1 OR a.Outcome = 10) 
				AND c.TestCaseName LIKE @NameFilter
				AND (@IncludeWarmupCooldown = 1 OR a.InMeasurementInterval = 1) 
			GROUP BY c.TestCaseName, a.TestCaseId

		UPDATE @Results SET 
			[90Percent] = (SELECT CAST(MIN(LTD.ElapsedTime) AS Decimal(15,2)) 
							FROM (select top 10 percent ElapsedTime
									from LoadTestTestDetail 
									where LoadTestRunId = @RunId
									AND TestCaseId = r.TestCaseId
									AND (@IncludeFailedTests = 1 OR Outcome = 10) 
									AND (@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
											AND (EndTime < DATEADD(second, @EndOffset, @StartTime))))
									AND (@IncludeWarmupCooldown = 1 OR InMeasurementInterval = 1) 
									order by ElapsedTime desc) as LTD),
			[95Percent] = (SELECT CAST(MIN(LTD.ElapsedTime) AS Decimal(15,2)) 
							FROM (select top 5 percent ElapsedTime
									from LoadTestTestDetail 
									where LoadTestRunId = @RunId
									AND TestCaseId = r.TestCaseId
									AND (@IncludeFailedTests = 1 OR Outcome = 10) 
									AND (@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
											AND (EndTime < DATEADD(second, @EndOffset, @StartTime))))
									AND (@IncludeWarmupCooldown = 1 OR InMeasurementInterval = 1) 
									order by ElapsedTime desc) as LTD)
		FROM @Results r
					
		IF @IncludeFailedTests = 0
		BEGIN
			IF @OrderByAverageTime = 1
			BEGIN
				SELECT TOP (@NumberOfRows) [id],[Name],[Passed] AS Quantity,[Minimum],[Average],[Maximum],[Std Dev], [90Percent], [95Percent] 
				FROM @Results ORDER BY [Average] DESC
			END
			ELSE
			BEGIN
				SELECT TOP (@NumberOfRows) [id],[Name],[Passed] AS Quantity,[Minimum],[Average],[Maximum],[Std Dev], [90Percent], [95Percent] 
				FROM @Results ORDER BY [Name] DESC
			END	
		END
		ELSE --@IncludeFailedTests = 1
		BEGIN
			IF @OrderByAverageTime = 1
			BEGIN
				SELECT TOP (@NumberOfRows) [Name],[Total] AS Quantity,[Passed],([Total] - [Passed]) AS [Failed],[Minimum],[Average],[Maximum],[Std Dev], [90Percent], [95Percent] 
				FROM @Results ORDER BY [Average] DESC
			END
			ELSE
			BEGIN
				SELECT TOP (@NumberOfRows) [Name],[Total] AS Quantity,[Passed],([Total] - [Passed]) AS [Failed],[Minimum],[Average],[Maximum],[Std Dev], [90Percent], [95Percent] 
				FROM @Results ORDER BY [Name] DESC
			END	
		END
	GO
	--START CODE--
	GRANT EXECUTE ON TSL_prc_Interval_TestResults TO PUBLIC
	GO

/*== TSL_prc_Interval_TransactionResults ========================================================
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE TSL_prc_Interval_TransactionResults
		@RunId  INT
		,@StartOffset INT = NULL
		,@EndOffset INT = NULL
		,@NameFilter nvarchar(255) = '%'
		,@IncludeFailedTransactions INT = 0   -- This param is ignored, but exists to allow the passed in arguments to be the same for all 3 procs
		,@OrderByAverageTime INT = 1
		,@IncludeWarmupCooldown INT = 0
		,@NumberOfRows INT = 25
	AS

		DECLARE @StartTime DATETIME
		SET @StartTime = (SELECT StartTime FROM LoadTestRun WHERE LoadTestRunId = @RunId)

			DECLARE @Results TABLE ([id] INT, [Name] NVARCHAR(255), [Total] INT,
				[Minimum] DECIMAL(15,2),[Average] DECIMAL(15,2), [Maximum] DECIMAL(15,2), 
				[Std Dev] DECIMAL(15,2), [90Percent] DECIMAL(15,2),	[95Percent] DECIMAL(15,2))
	
			INSERT INTO @Results 
			([id],[Name],[Total],[Minimum],[Average],[Maximum],[Std Dev])
				SELECT
					c.TransactionId
					,d.TransactionName
					,COUNT(DISTINCT (c.TransactionDetailId)) AS [Total]
					,CAST(MIN(c.ResponseTime) AS Decimal(15,2)) AS [Min]
					,CAST(AVG(c.ResponseTime) AS Decimal(15,2)) AS [Avg]
					,CAST(MAX(c.ResponseTime) AS Decimal(15,2)) AS [Max]
					,CAST(STDEV(c.ResponseTime) AS Decimal(15,2)) AS [Std Dev]
				FROM 
					dbo.LoadTestTestDetail AS a
				INNER JOIN 
					LoadTestRun AS b 
					ON a.LoadTestRunId = b.LoadTestRunId
				INNER JOIN dbo.LoadTestTransactionDetail AS c 
					ON a.LoadTestRunId = c.LoadTestRunId 
					AND a.TestDetailId = c.TestDetailId
				INNER JOIN dbo.WebLoadTestTransaction AS d
					ON a.LoadTestRunId = d.LoadTestRunId
					AND c.TransactionId = d.TransactionId
				WHERE 
					a.LoadTestRunId = @RunId
					AND (@StartOffset IS NULL OR c.EndTime >= DATEADD(second, @StartOffset, b.StartTime))
					AND (@EndOffset IS NULL OR c.EndTime < DATEADD(second, @EndOffset, b.StartTime))
					AND d.TransactionName LIKE @NameFilter
					AND (@IncludeWarmupCooldown = 1 OR a.InMeasurementInterval = 1) 
				GROUP BY d.TransactionName, c.TransactionId

			UPDATE @Results SET 
			[90Percent] = (SELECT CAST(MIN(LTD.ResponseTime) AS Decimal(15,2)) 
							FROM (select top 10 percent ResponseTime
									from LoadTestTransactionDetail 
									where LoadTestRunId = @RunId
									AND TransactionId = r.Id
									AND (@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
										AND (EndTime < DATEADD(second, @EndOffset, @StartTime))))
									AND (@IncludeWarmupCooldown = 1 OR InMeasurementInterval = 1) 
									order by ResponseTime desc) as LTD),
			[95Percent] = (SELECT CAST(MIN(LTD.ResponseTime) AS Decimal(15,2)) 
							FROM (select top 5 percent ResponseTime
									from LoadTestTransactionDetail 
									where LoadTestRunId = @RunId
									AND TransactionId = r.Id
									AND (@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
										AND (EndTime < DATEADD(second, @EndOffset, @StartTime))))
									AND (@IncludeWarmupCooldown = 1 OR InMeasurementInterval = 1) 
									order by ResponseTime desc) as LTD)
		FROM @Results AS r
		
		IF @OrderByAverageTime = 1
		BEGIN
			SELECT TOP (@NumberOfRows) [Id],[Name],[Total] AS Quantity,[Minimum],[Average],[Maximum],[Std Dev], [90Percent], [95Percent] 
			FROM @Results ORDER BY [Average] DESC
		END
		ELSE
		BEGIN
			SELECT TOP (@NumberOfRows) [Id],[Name],[Total] AS Quantity,[Minimum],[Average],[Maximum],[Std Dev], [90Percent], [95Percent] 
			FROM @Results ORDER BY [Name] DESC
		END	
	GO
	--START CODE--
	GRANT EXECUTE ON TSL_prc_Interval_TransactionResults TO PUBLIC
	GO

/*== TSL_prc_Interval_PageResults ===============================================================
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE TSL_prc_Interval_PageResults
		@RunId  INT
		,@StartOffset INT = NULL
		,@EndOffset INT = NULL
		,@NameFilter nvarchar(255) = '%'
		,@IncludeFailedPages INT = 0
		,@OrderByAverageTime INT = 1
		,@IncludeWarmupCooldown INT = 0
		,@NumberOfRows INT = 25
	AS

		DECLARE @StartTime DATETIME
		SET @StartTime = (SELECT StartTime FROM LoadTestRun WHERE LoadTestRunId = @RunId)



		DECLARE @Results TABLE ([id] int, [Name] NVARCHAR(255), [Total] INT, [Passed] INT,
			[Minimum] DECIMAL(15,2),[Average] DECIMAL(15,2), [Maximum] DECIMAL(15,2), 
			[Std Dev] DECIMAL(15,2), [90Percent] DECIMAL(15,2),	[95Percent] DECIMAL(15,2))

		INSERT INTO @Results 
		([id],[Name],[Total],[Passed],[Minimum],[Average],[Maximum],[Std Dev])
			SELECT
				c.PageId
				,d.RequestUri
				,COUNT(DISTINCT (c.PageDetailId))
				,COUNT(DISTINCT (c2.PageDetailId))
				,CAST(MIN(c2.ResponseTime) AS Decimal(15,2))
				,CAST(AVG(c2.ResponseTime) AS Decimal(15,2))
				,CAST(MAX(c2.ResponseTime) AS Decimal(15,2))
				,CAST(STDEV(c2.ResponseTime) AS Decimal(15,2))
			FROM 
				dbo.LoadTestTestDetail AS a
			INNER JOIN 
				LoadTestRun AS b 
				ON a.LoadTestRunId = b.LoadTestRunId
			INNER JOIN dbo.LoadTestPageDetail AS c 
				ON a.LoadTestRunId = c.LoadTestRunId 
				AND a.TestDetailId = c.TestDetailId

			LEFT OUTER JOIN LoadTestPageDetail AS c2
				ON c.LoadTestRunId = c2.LoadTestRunId
				AND c.PageDetailId = c2.PageDetailId
				AND c2.Outcome = 0

			INNER JOIN dbo.WebLoadTestRequestMap AS d
				ON a.LoadTestRunId = d.LoadTestRunId
				AND c.PageId = d.RequestId
			WHERE 
				a.LoadTestRunId = @RunId
				AND (@StartOffset IS NULL OR c.EndTime >= DATEADD(second, @StartOffset, b.StartTime))
				AND (@EndOffset IS NULL OR c.EndTime < DATEADD(second, @EndOffset, b.StartTime))
				AND d.RequestUri LIKE @NameFilter
				AND (@IncludeFailedPages = 1 OR c.Outcome = 0) 
				AND (@IncludeWarmupCooldown = 1 OR a.InMeasurementInterval = 1) 
			GROUP BY d.RequestUri, c.PageId

		UPDATE @Results SET 
			[90Percent] = (select CAST(MIN(ResponseTime) AS Decimal(15,2)) from 
				(select top 10 percent ResponseTime 
					from LoadTestPageDetail 
					where LoadTestRunId = @RunId 
					AND PageId=[id]
					AND (@IncludeFailedPages = 1 OR Outcome = 0) 
					AND (@IncludeWarmupCooldown = 1 OR InMeasurementInterval = 1) 
					AND 
						(@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
						AND
						(EndTime < DATEADD(second, @EndOffset, @StartTime))))
					order by ResponseTime desc) as LTD),
			[95Percent] = (select CAST(MIN(ResponseTime) AS Decimal(15,2)) from 
				(select top 5 percent ResponseTime 
					from LoadTestPageDetail 
					where LoadTestRunId = @RunId 
					AND PageId=[id]
					AND (@IncludeFailedPages = 1 OR Outcome = 0) 
					AND (@IncludeWarmupCooldown = 1 OR InMeasurementInterval = 1) 
					AND 
						(@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
						AND
						(EndTime < DATEADD(second, @EndOffset, @StartTime))))
					order by ResponseTime desc) as LTD)
		FROM @Results AS r

		IF @IncludeFailedPages = 0
		BEGIN
			IF @OrderByAverageTime = 1
			BEGIN
				SELECT TOP (@NumberOfRows) [Id], [Name],[Passed] AS Quantity,[Minimum],[Average],[Maximum],[Std Dev], [90Percent], [95Percent] 
				FROM @Results ORDER BY [Average] DESC
			END
			ELSE
			BEGIN
				SELECT TOP (@NumberOfRows) [Id], [Name],[Passed] AS Quantity,[Minimum],[Average],[Maximum],[Std Dev], [90Percent], [95Percent] 
				FROM @Results ORDER BY [Name] DESC
			END	
		END
		ELSE --@IncludeFailedPages = 1
		BEGIN
			IF @OrderByAverageTime = 1
			BEGIN
				SELECT TOP (@NumberOfRows) [Id], [Name],[Total] AS Quantity,[Passed],([Total] - [Passed]) AS [Failed],[Minimum],[Average],[Maximum],[Std Dev], [90Percent], [95Percent] 
				FROM @Results ORDER BY [Average] DESC
			END
			ELSE
			BEGIN
				SELECT TOP (@NumberOfRows) [Id], [Name],[Total] AS Quantity,[Passed],([Total] - [Passed]) AS [Failed],[Minimum],[Average],[Maximum],[Std Dev], [90Percent], [95Percent] 
				FROM @Results ORDER BY [Name] DESC
			END	
		END
	GO
	--START CODE--
	GRANT EXECUTE ON TSL_prc_Interval_PageResults TO PUBLIC
	GO







